# -*- coding: utf-8 -*-

# author: Gabriel Mickel
# email: g3mickel@gmail.com

import os
from datetime import datetime
import MySQLdb

from Hunter_Crawler.settings import HOST, PORT, USER, PASS, DB
from Hunter_Crawler.items import JingdongReview, JingdongProduct, JingdongProductUpdate

class Jingdong_SQL_Pipeline(object):
	'''store to MySQL'''
	
	def open_spider(self, spider):
		self.conn = MySQLdb.connect(host=HOST, port=PORT,user=USER,passwd=PASS,charset='utf8')
		self.conn.select_db(DB)
		self.cur = self.conn.cursor()

	def process_item(self, item, spider):
#		self.cur.excecute("SELECT * FROM Jingdong_basic_info;")

		if isinstance(item, JingdongReview):
			self.cur.execute('select review_id from review where review_ID = "%s"' % item['reviewID'])
			if len(self.cur.fetchall()) == 0: 
				to_insert = ("INSERT INTO review (review_id, product_id, star, time, helpful, customer_name, customer_link, content, tags) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)")
				self.cur.execute(to_insert, (item['reviewID'],item['productID'],int(item['star']),item['date'],item['useful'],item['customer'],item['customerLink'],item['content'],'|'.join(item['tags'])))
				self.conn.commit()

		if isinstance(item, JingdongProduct):
			self.cur.execute('select id from review where product_id = "%s"' % item['productID'])
			if len(self.cur.fetchall()) == 0:
				to_insert = ('INSERT INTO product (product_id, name, category, website, brand_name, brand_id, update_date, price, sale_rank, avg_rating, num_reviews, last_crawled_date)  VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)')
				self.cur.execute(to_insert, (item['productID'],item['name'],item['category'],item['website'],item['brandName'],item['brandID'],item['update_date'],item['price'],item['sale_rank'],item['avg_rating'],item['num_reviews'],item['last_crawled_date']))
#				print(to_insert % (item['productID'],item['name'],item['category'],item['brandName'],item['brandID'],item['update_date'],item['price']))
				self.conn.commit()

		if isinstance(item, JingdongProductUpdate):
			to_insert = ('update product set avg_rating="%s", num_reviews="%s", last_crawled_date="%s", sale_rank="%s" where product_id = "%s" ')
			self.cur.execute(to_insert, (item['avg_rating'],item['num_reviews'],item['last_crawled_date'],item['sale_rank'],item['productID']))
			self.conn.commit()

		return item
		'''
	def close_spider(self, spider):
		self.cur.close()
		self.conn.close()
'''

class Jingdong_File_Pipeline(object):
	def open_spider(self, spider):
		if not os.path.isdir(ROOT + "/Data/"):
			os.makedirs(ROOT + "/Data/")
		self.curDate = datetime.now().strftime("%Y%m%d")

	def process_item(self, item, spider):

		if isinstance(item, JingdongProduct):
			if not os.path.isdir(ROOT + "/Data/Products/" + item['category'] + "/"): 
				os.makedirs(ROOT + "/Data/Products/" + item['category'] + "/")
			with open(ROOT + "/Data/Products/" + item['category'] + "/" + item['productID'] + ".dat", 'wb') as product_ :
				product_.write('ProductID: ' + item['productID'] + '\n')
				product_.write('Name: ' + item['name'] + '\n')
				product_.write('Category: ' + item['name'] + '\n')
				product_.write('Brand: ' + item['brand'] + '\n')
				product_.write('Website: ' + item['website'] + '\n')
				product_.write('Sale rank: ' + item['sale_rank'] + '\n')
				product_.write('Average rating: ' + item['avg_rating'] + '\n')
				product_.write('Number of reviews: ' + item['num_reviews'] + '\n')
				product_.write('Product features: ' + ', '.join(item['product_features']) + '\n')
				product_.write('Update date: ' + item['update_date'] + '\n')
				product_.write('Description: ' + item['desc'] + '\n')

		if isinstance(item, JingdongReview):
			if not os.path.isdir(ROOT + "/Data/Reviews/" + item['category'] + '/'):
				os.makedirs(ROOT + "/Data/Reviews/" + item['category'] + '/')
			with open(ROOT + "/Data/Reviews/" + item['category'] + '/') as review_:
				review_.write('ReviewID: ' + item['reviewID'] + '\n')
				review_.write('ProductID: ' + item['productID'] + '\n')
                review_.write('Stars: ' + item['star'] + '\n')
                review_.write('Review Date: ' + item['date'] + '\n')
                review_.write('Helpful Votes: ' + item['helpful'] + '\n')
                review_.write('Customer Name: ' + item['customer'] + '\n')
                review_.write('Review Content: ' + item['content'] + '\n')
                review_.write('Tags: ' + ', '.join(item['tags']) + '\n')

